# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
获取中移动项目的运营还款统计数据:
1.授信成功、失败、汇总信息； 2.借款成功、失败、汇总信息；3.办理业务成功记录
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from msydwork.prod.pro_config import cursor
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachobase.deal_excel import row_commonWrite,copy_sheetWrite


# 还款,当天日期
def get_repayCount(count,countC):
    sql = 'select "%s","repay","S",count(*),sum(repay_principal),sum(repay_interest),sum(repay_penalty),sum(repay_amt) from ms_repay_order where status="S" and substr(start_time,1,10)%s"%s" UNION select "%s","repay","F",count(*),sum(repay_principal),sum(repay_interest),sum(repay_penalty),sum(repay_amt) from ms_repay_order where status="F" and substr(start_time,1,10)%s"%s";' % (td_date,count,td_date,td_date,countC,td_dateC)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            row.append(data)          # 多条数据
        info.append(row)
    #print(info)
    return info


# 还款,当天日期 汇总
def get_repayTotal(count,countC):
    sql = 'select "%s","repay","total",count(*),sum(repay_principal),sum(repay_interest),sum(repay_penalty),sum(repay_amt) from ms_repay_order where substr(start_time,1,10)%s"%s" UNION select "%s","repay","N",count(*),sum(repay_principal),sum(repay_interest),sum(repay_penalty),sum(repay_amt) from ms_repay_order where status="N" and substr(start_time,1,10)%s"%s";' % (td_date,count,td_date,td_date,countC,td_dateC)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            row.append(data)          # 多条数据
        info.append(row)
    #print(info)
    return info


# 还款所有,昨天日期(所有文件已推送的)
def get_repayInfo(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.brw_ord_no,b.process_no,c.amt,c.dep_id,c.dep_nm,substr(acp_dt,1,10),c.file_prov_no,b.status,b.rpy_result_inf,b.callback_time,b.repay_amt,b.repay_principal,b.repay_interest,b.repay_penalty,b.rpy_mod,b.rpy_seq from hb_usr a,ms_repay_order b,hb_loanfile_detail c where a.hb_usr_no=b.hb_usr_no and b.brw_ord_no=c.mpl_ord_no and substr(start_time,1,10)%s"%s" order by b.status desc,callback_time desc;'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(info)
    return info

# 还款所有,昨天日期(文件未推送的,接口推送)
def get_repayInfo_notFile(count,td_date):
    sql = 'select a.hb_usr_no,a.mbl_no,a.usr_id_card,a.usr_id_name,b.brw_ord_no,b.process_no,a.loan_amt,c.dep_id,c.dep_nm,substr(c.acp_tm,1,10),a.usr_prov_no,b.status,b.rpy_result_inf,b.callback_time,b.repay_amt,b.repay_principal,b.repay_interest,b.repay_penalty,b.rpy_mod,b.rpy_seq from hb_usr a,ms_repay_order b,pkg_notice c where a.hb_usr_no=b.hb_usr_no and b.brw_ord_no=c.brw_ord_no and substr(start_time,1,10)%s"%s" and c.brw_ord_no not in (select mpl_ord_no from hb_loanfile_detail) order by b.status desc,callback_time desc;'%(count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    i = 1
    for result in results:
        row = []
        row.append(i)
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
        i = i + 1
    #print(info)
    return info


# 逾期未还,截止到某个日期
def get_overdueInfo(td_date):
    sql = 'select a.id as 借据id,c.hb_usr_no,c.mbl_no,c.usr_id_card,c.usr_id_name,a.outside_process_no,a.process_no,b.loan_init_prin,d.dep_id,d.dep_nm,substr(acp_dt,1,10),d.file_prov_no,b.repay_plan_no,b.loan_pmt_due_date,a.overdue_date,"%s",b.curr_term,b.loan_term_prin,b.loan_term_int from ms_loan a,ms_repayment_plan b,hb_usr c,hb_loanfile_detail d where a.id=b.loan_id and a.hb_user_no=c.hb_usr_no and a.outside_process_no=d.mpl_ord_no and b.loan_pmt_due_date<"%s" and b.repay_status=0 and a.status=6 order by a.id,b.loan_term_int asc;'%(td_date,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%-m%-d%')
            except:
                data = str(data)
            row.append(data)
        info.append(row)
    #print(info)
    return info


def main(count):
    countC = count
    two_list =[]
    two_list.append(['','','',td_date+'还款信息汇总'])

    two_list.append(['\n'])
    two_list.append(['还款日期','备注','还款状态','数量','还款本金','还款利息','还款罚息','还款总金额'])
    repayCount = get_repayCount(count, countC)
    for info in repayCount:
        two_list.append(info)
    repayTotal = get_repayTotal(count, countC)
    for info in repayTotal:
        two_list.append(info)

    two_list.append(['\n'])
    two_list.append(['还款信息：文件未推送、办理套餐成功接口推送的所有订单的还款（一般是当天办理当天退货）',''])
    two_list.append(['序号','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份','还款状态','还款说明','还款确认时间','还款总金额','还款本金','还款利息','还款罚息','还款类型','还款期数'])
    repayInfo_notFile = get_repayInfo_notFile(count, td_date)
    for info in repayInfo_notFile:
        two_list.append(info)

    two_list.append(['\n'])
    two_list.append(['还款信息：文件推送的所有订单的还款',''])
    two_list.append(['序号','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份','还款状态','还款说明','还款确认时间','还款总金额','还款本金','还款利息','还款罚息','还款类型','还款期数'])
    repayInfo = get_repayInfo(count, td_date)
    for info in repayInfo:
        two_list.append(info)

    two_list.append(['\n'])
    two_list.append(['截止到'+td_date+'的逾期还款信息', ''])
    two_list.append(['借据id','和包号','注册手机号','身份证号','姓名','和包借款订单','小贷借款订单','借款金额','营业厅编号','营业厅名称','办理业务成功日期','业务办理省份','还款计划编号','到期还款日','逾期日期','当前日期','逾期期数','逾期本金','逾期利息'])
    overdueInfo = get_overdueInfo(td_date)
    for info in overdueInfo:
        two_list.append(info)

    print(two_list)
    old_file = '中移动还款' + old_name + '.xls'
    if count=='<=':
        file = '中移动还款汇总' + sheet_name + '.xls'
        row_commonWrite(two_list, file_path,file, sheet_name)                              # 直接生成新的
    else:
        file = '中移动还款' + sheet_name +'.xls'
        try:
            copy_sheetWrite(two_list, file_path, old_file,file,sheet_name)                 # 如果前一天没有文件，就生成新文件; 有就复制
        except:
            row_commonWrite(two_list, file_path, file,sheet_name)


if __name__ == '__main__':
    t = 0  # t=0 今天，t=1截止到昨天
    file_path = 'E:\zenglingwei\statistics\\'
    td_date = minus_date(t).strftime('%Y-%m-%d')  # 当天日期
    td_dateC = td_date
    yd_date = minus_date(t).strftime('%Y-%m-%d')  # 昨天日期

    sheet_name = minus_date(t).strftime('%Y%m%d')
    old_name = minus_date(t + 1).strftime('%Y%m%d')

    main("=")             # = ,<=
